const { queryMysql } = require("../utils/queryMysqlUtil");
const dayjs = require("dayjs");
require("dayjs/locale/zh-cn");
dayjs.locale("zh-cn");

const path = require("path");
const fs = require("fs");
const dailyTpl = fs.readFileSync(
  path.resolve(__dirname, "../templates/dailyTpl.html"),
  "utf-8"
);
const artTemplate = require("art-template");

/**
 * 获取branchId部门下，指定时间内，所有组汇总的统计数据
 * @param {number} branchId  部门id
 * @param {string} startNyrDate  起始时间（年月日）
 * @param {string} endNyrDate  结束时间（年月日）
 * @returns {Promise<Array<{group_id:number,group_name:string, add_num: string|null, remove_num: string|null, total_num: string|null }>>}
 */
async function getGroupOverviewDataByBranchId(
  branchId,
  startNyrDate,
  endNyrDate
) {
  const queryStr = `
    select chihiro_team_group.id as group_id, chihiro_team_group.group_name as group_name, sum(add_num) as add_num,sum(remove_num) as remove_num,sum(total_num) as total_num
    from chihiro_branch
    left join chihiro_team_group
    on chihiro_branch.id=chihiro_team_group.branch_id
    left join chihiro_gitlab_detail
    on chihiro_team_group.id=chihiro_gitlab_detail.group_id
    left join (
        select * 
        from chihiro_code_count
        where chihiro_code_count.create_time between '${startNyrDate} 00:00:00' and '${endNyrDate} 23:59:59'
    ) temp_chihiro_code_count
    on chihiro_gitlab_detail.id=temp_chihiro_code_count.repo_rid
    where chihiro_branch.id=${branchId} 
    group by chihiro_team_group.id,chihiro_team_group.group_name;
  `;

  let res = await queryMysql(queryStr);
  return res.results;
}

/**
 * 获取branchId部门下，指定时间内，所有组统计数据求和
 * @param {number} branchId  部门id
 * @param {string} startNyrDate  起始时间（年月日）
 * @param {string} endNyrDate  结束时间（年月日）
 * @returns {Promise<{ add_num: string|null, remove_num: string|null, total_num: string|null }>}
 */
async function getAllGroupOverviewDataByBranchId(
  branchId,
  startNyrDate,
  endNyrDate
) {
  const queryStr = `
    select sum(add_num) as add_num,sum(remove_num) as remove_num,sum(total_num) as total_num
    from chihiro_branch
    left join chihiro_team_group
    on chihiro_branch.id=chihiro_team_group.branch_id
    left join chihiro_gitlab_detail
    on chihiro_team_group.id=chihiro_gitlab_detail.group_id
    left join (
        select * 
        from chihiro_code_count
        where chihiro_code_count.create_time between '${startNyrDate} 00:00:00' and '${endNyrDate} 23:59:59'
    ) temp_chihiro_code_count
    on chihiro_gitlab_detail.id=temp_chihiro_code_count.repo_rid
    where chihiro_branch.id=${branchId} 
  `;

  let res = await queryMysql(queryStr);
  return res.results[0];
}

/**
 * 通过组id查询其下所有项目在【startNyrDate，endNyrDate】期间的统计数据。
 * 按照项目分组聚合。去除新增、删除、总计全部为0的项目
 * @param {number} groupId 组id
 * @param {string} startNyrDate
 * @param {string} endNyrDate
 * @returns {Promise<Array<{gitlab_name:string,add_num:string,remove_num:string,total_num:string}>>}
 */
async function getDetailGroupDataByGroupIdWithoutZero(
  groupId,
  startNyrDate,
  endNyrDate
) {
  const queryStr = `
        select gitlab_name,add_num,remove_num,total_num
        from chihiro_team_group
        left join chihiro_gitlab_detail
        on chihiro_team_group.id=chihiro_gitlab_detail.group_id
        join (
            select *
            from chihiro_code_count
            where create_time between '${startNyrDate} 00:00:00' and '${endNyrDate} 23:59:59' and (add_num<>0 or remove_num<>0 or total_num<>0)
        ) temp_chihiro_code_count
        on chihiro_gitlab_detail.id=temp_chihiro_code_count.repo_rid
        where chihiro_team_group.id=${groupId} 
  `;

  let res = await queryMysql(queryStr);
  return res.results;
}

/**
 * 指定部门下的组信息
 * @param branchId 部门id
 * @returns {Promise<Array<{group_id:number,group_name:string}>>}
 */
async function getGroupByBranchId(branchId) {
  const queryStr = `
        select id as group_id,group_name
        from chihiro_team_group
        where chihiro_team_group.branch_id=${branchId}
  `;

  let res = await queryMysql(queryStr);
  return res.results;
}

/**
 * 返回日报数据
 * @param branchId 部门id
 * @param nyrDate 日报日期（年月日）
 * @returns {Promise<string>}
 */
async function getRenderDailyTpl(branchId, nyrDate) {
  // const groupNameList = [
  //   "基础+小程序组",
  //   "网站研发组",
  //   "系统研发组",
  //   "跨平台应用组",
  //   "业务服务组",
  //   "原生应用组（Android）",
  //   "原生应用组（iOS）",
  // ];

  //nyrDate当日数据
  // let todayGroupOverviewList = await getGroupOverviewDataByBranchId(
  //   branchId,
  //   nyrDate,
  //   nyrDate
  // );

  //branchId=1下的所有小组
  let groupList = await getGroupByBranchId(1);

  //这里特殊处理下,如果存在'基础+小程序组'小组，把他放在最前面
  let specIndex = groupList.findIndex((item) => {
    return item.group_name === "基础+小程序组";
  });
  if (specIndex !== -1) {
    const rest = groupList.filter(
      (item) => item.group_name !== "基础+小程序组"
    );
    groupList = [groupList[specIndex], ...rest];
  }

  // const todayAllGroupOverviewSum = await getAllGroupOverviewDataByBranchId(
  //   branchId,
  //   nyrDate,
  //   nyrDate
  // );

  // const todayAverage = Math.round(todayAllGroupOverviewSum.total_num / 55);

  //nyrDate所在这一周数据
  const weekStartNyrDate = dayjs(nyrDate).startOf("week").format("YYYY-MM-DD");
  const weekEndNyrDate = dayjs(nyrDate).endOf("week").format("YYYY-MM-DD");
  const weekAllGroupOverviewSum = await getAllGroupOverviewDataByBranchId(
    branchId,
    weekStartNyrDate,
    weekEndNyrDate
  );

  const weekAverage = Math.round(weekAllGroupOverviewSum.total_num / 55);

  //nyrDate当日数据（详细）
  let detailGroupDate = {};
  let todayGroupOverviewList = [];
  for (let item of groupList) {
    detailGroupDate[item.group_name] =
      await getDetailGroupDataByGroupIdWithoutZero(
        item.group_id,
        nyrDate,
        nyrDate
      );
    let add_num = 0,
      remove_num = 0,
      total_num = 0;
    detailGroupDate[item.group_name].forEach((repoItem) => {
      add_num += repoItem.add_num;
      remove_num += repoItem.remove_num;
      total_num += repoItem.total_num;
    });
    todayGroupOverviewList.push({
      group_name: item.group_name,
      add_num,
      remove_num,
      total_num,
    });
  }

  let todayAllGroupOverviewSum = {};
  let all_group_add_num = 0,
    all_group_remove_num = 0,
    all_group_total_num = 0;
  todayGroupOverviewList.forEach((groupItem) => {
    all_group_add_num += groupItem.add_num;
    all_group_remove_num += groupItem.remove_num;
    all_group_total_num += groupItem.total_num;
  });
  todayAllGroupOverviewSum = {
    add_num: all_group_add_num,
    remove_num: all_group_remove_num,
    total_num: all_group_total_num,
  };

  const todayAverage = Math.round(todayAllGroupOverviewSum.total_num / 55);

  return artTemplate.render(dailyTpl, {
    nyrDate: nyrDate,
    todayGroupOverviewList,
    todayAllGroupOverviewSum,
    todayAverage,
    weekAllGroupOverviewSum,
    weekAverage,
    detailGroupDate,
  });
}

module.exports = {
  getGroupOverviewDataByBranchId,
  getAllGroupOverviewDataByBranchId,
  getDetailGroupDataByGroupIdWithoutZero,
  getRenderDailyTpl,
};
